package com.bringway.p2p.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.p2pZcxxDao;

import net.sf.json.JSONObject;
/**  
 * @类名 p2pZcxxDaoImpl  
 * @功能 TODO  注册信息查询
 * @作者 js 
 * @日期 2016年11月15日 下午2:23:37  
 *    
 */
@Repository
public class p2pZcxxDaoImpl extends P2PJdbcDao implements p2pZcxxDao {
	Logger logger=Logger.getLogger(p2pZcxxDaoImpl.class);

	/* 
	 * @方法名  queryZcxxList 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.p2pZcxxDao#queryZcxxList(net.sf.json.JSONObject)  
	 */
	@Override
	public List<?> queryZcxxList(JSONObject params) {
		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String startDate = params.optString("regStartDate");
		String endDate = params.optString("regEndDate");
		String user_name = params.optString("user_name");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String realName = params.optString("realName");
		String sfzNum = params.optString("sfzNum");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append("select temp4.*,tt4.GYH OA_GYH,tt4.DQ,tt4.BM,tt4.SS,tt4.ZW,tt4.ZJ,tt4.XM OA_XM,tt4.SJHM OA_SJHM,TT5.TGCODE TGM,TT5.TJR_TGCODE TJR_TGM , TT7.USER_NAME username ,TT7.TJR tjr1 ,TT7.TJJB tjjb1");
		sql.append(" from (select temp3.* ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" case when temp3.tjr2 is null or temp3.tjr2='' then case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end else temp3.tjr2 end else temp3.tjr1 end TJR");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" case when temp3.tjr3 is null or temp3.tjr3='' then null else '03' end else '02' end else '01' end TJJB");
		sql.append(" from (select temp2.* ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(" (select tu3.tjr from t_zhfw_user_tgcode tg3 inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE inner join t_zhfw_user tu3");
		sql.append(" on tg1.USER_NAME=tu3.USER_NAME where tg3.USER_NAME=temp2.USER_NAME) end tjr3");
		sql.append(" from (SELECT TEMP.ID,TEMP.USER_NAME,TEMP.SJHM,tt1.REAL_NAME,tt1.SFZ_NUM,date_format(TEMP.CJRQ,'%Y-%m-%d %H:%i:%s') CJRQ,TEMP.CJ_TYPE,TEMP.TJR tjr1");
		sql.append(",case when TEMP.TJR is null or TEMP.TJR = '' then (select tu2.tjr from t_zhfw_user_tgcode tg2 inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME where tg2.USER_NAME=TEMP.USER_NAME) end tjr2");
		sql.append(" from t_zhfw_user TEMP LEFT JOIN t_zhfw_smrz tt1 on TEMP.USER_NAME = tt1.USER_NAME");
		sql.append(" where 1=1  and TEMP.DISABLED ='1'");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(TEMP.CJRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(TEMP.CJRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(user_name)) {
			sql.append(" AND TEMP.USER_NAME LIKE :user_name");
			paramMap.put("user_name", "%"+user_name+"%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND TEMP.SJHM LIKE :sjhm");
			paramMap.put("sjhm", "%"+sjhm+"%");
		}
		if (StringUtils.isNotEmpty(realName)) {
			sql.append(" AND tt1.REAL_NAME LIKE :realName");
			paramMap.put("realName", "%"+realName+"%");
		}
		if (StringUtils.isNotEmpty(sfzNum)) {
			sql.append(" AND tt1.SFZ_NUM LIKE :sfzNum");
			paramMap.put("sfzNum", "%"+sfzNum+"%");
		}
		sql.append(") temp2) temp3) temp4");
		sql.append(" left join t_zhfw_user_tgcode TT5 on TT5.USER_NAME=temp4.USER_NAME");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR left join t_zhfw_tjr_tjjb TT7 on TT7.USER_NAME=TT5.USER_NAME  where 1=1");
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs=StringUtils.split(tjr, ",");
			String[] sqls=new String[tjrs.length];
			for (int i=0;i<tjrs.length;i++) {
				sqls[i]="temp4.TJR like :tjr"+i;
				paramMap.put("tjr"+i,  tjrs[i]+"%");
			}
			sql.append(" AND ("+StringUtils.join(sqls, " OR ")+")");
		}
		sql.append(" order by temp4.CJRQ desc");
		if (start>=0&&limit>0) {
			sql.append(" limit :start,:limit");
			paramMap.put("start", start);
			paramMap.put("limit", limit);
		}
		logger.info("queryZcxxList sql is " + sql.toString());
		logger.info("queryZcxxList paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForList(sql.toString(), paramMap);

	}

	/* 
	 * @方法名  getZcxxCount 
	 * @功能   TODO
	 * @param params
	 * @return  
	 * @see com.bringway.p2p.dao.p2pZcxxDao#getZcxxCount(net.sf.json.JSONObject)  
	 */
	@Override
	public int getZcxxCount(JSONObject params) {
		String startDate = params.optString("regStartDate");
		String endDate = params.optString("regEndDate");
		String user_name = params.optString("user_name");
		String sjhm = params.optString("sjhm");
		String tjr = params.optString("tjr");
		String realName = params.optString("realName");
		String sfzNum = params.optString("sfzNum");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append("select count(*) from (select temp3.* ,case when temp3.tjr1 is null or temp3.tjr1='' then");
		sql.append(" case when temp3.tjr2 is null or temp3.tjr2='' then case when temp3.tjr3 is null or temp3.tjr3='' then null");
		sql.append(" else temp3.tjr3 end else temp3.tjr2 end else temp3.tjr1 end TJR");
		sql.append(" ,case when temp3.tjr1 is null or temp3.tjr1='' then case when temp3.tjr2 is null or temp3.tjr2='' then");
		sql.append(" case when temp3.tjr3 is null or temp3.tjr3='' then null else '03' end else '02' end else '01' end TJJB");
		sql.append(" from (select temp2.* ,case when temp2.tjr2 is null or temp2.tjr2 = '' then");
		sql.append(" (select tu3.tjr from t_zhfw_user_tgcode tg3 inner join t_zhfw_user_tgcode tg2 on tg3.TJR_TGCODE=tg2.TGCODE");
		sql.append(" inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE inner join t_zhfw_user tu3");
		sql.append(" on tg1.USER_NAME=tu3.USER_NAME where tg3.USER_NAME=temp2.USER_NAME) end tjr3");
		sql.append(" from (SELECT TEMP.ID,TEMP.USER_NAME,TEMP.SJHM,tt1.REAL_NAME,tt1.SFZ_NUM,date_format(TEMP.CJRQ,'%Y-%m-%d %H:%i:%s') CJRQ,TEMP.CJ_TYPE,TEMP.TJR tjr1");
		sql.append(" ,case when TEMP.TJR is null or TEMP.TJR = '' then (select tu2.tjr from t_zhfw_user_tgcode tg2 inner join t_zhfw_user_tgcode tg1 on tg2.TJR_TGCODE=tg1.TGCODE");
		sql.append(" inner join t_zhfw_user tu2 on tg1.USER_NAME=tu2.USER_NAME where tg2.USER_NAME=TEMP.USER_NAME) end tjr2");
		sql.append(" from t_zhfw_user TEMP LEFT JOIN t_zhfw_smrz tt1 on TEMP.USER_NAME = tt1.USER_NAME");
		sql.append(" where 1=1  and TEMP.DISABLED ='1'");
		if (StringUtils.isNotEmpty(startDate)) {
			sql.append(" AND date_format(TEMP.CJRQ,'%Y-%m-%d') >= :startDate ");
			paramMap.put("startDate", startDate);
		}
		if (StringUtils.isNotEmpty(endDate)) {
			sql.append(" AND date_format(TEMP.CJRQ,'%Y-%m-%d') <= :endDate ");
			paramMap.put("endDate", endDate);
		}
		if (StringUtils.isNotEmpty(user_name)) {
			sql.append(" AND TEMP.USER_NAME LIKE :user_name");
			paramMap.put("user_name", "%"+user_name+"%");
		}
		if (StringUtils.isNotEmpty(sjhm)) {
			sql.append(" AND TEMP.SJHM LIKE :sjhm");
			paramMap.put("sjhm", "%"+sjhm+"%");
		}
		if (StringUtils.isNotEmpty(realName)) {
			sql.append(" AND tt1.REAL_NAME LIKE :realName");
			paramMap.put("realName", "%"+realName+"%");
		}
		if (StringUtils.isNotEmpty(sfzNum)) {
			sql.append(" AND tt1.SFZ_NUM LIKE :sfzNum");
			paramMap.put("sfzNum", "%"+sfzNum+"%");
		}
		sql.append(") temp2) temp3) temp4");
		sql.append(" left join T_ZHFW_OA_USER TT4 on TT4.GYH=temp4.TJR where 1=1");
		if (StringUtils.isNotEmpty(tjr)) {
			String[] tjrs=StringUtils.split(tjr, ",");
			String[] sqls=new String[tjrs.length];
			for (int i=0;i<tjrs.length;i++) {
				sqls[i]="temp4.TJR like :tjr"+i;
				paramMap.put("tjr"+i,  tjrs[i]+"%");
			}
			
			sql.append(" AND ("+StringUtils.join(sqls, " OR ")+")");
		}
		logger.info("getZcxxCount sql is " + sql.toString());
		logger.info("getZcxxCount paramMap is " + paramMap.toString());
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}

}
